﻿//2013-12-09
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using PSS.Entity.SetManage.BasicData;
using PSS.Utility;

namespace PSS.DAL.SetManage.BasicData
{
    public class VendorDAL : DbHelperBase
    {
        public bool AddVendor(VendorEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [PSS_Vendor]([VendorId],[VendorCode],[VendorName],[VCategoryId],[BalanceDate],[Payable],[Advance],[VATRate],[Remark]) ");
            strSql.Append(" VALUES(@VendorId,@VendorCode,@VendorName,@VCategoryId,@BalanceDate,@Payable,@Advance,@VATRate,@Remark) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@VendorId", model.VendorId);
                    Db.AddInParameter(cmd, "@VendorCode", model.VendorCode);
                    Db.AddInParameter(cmd, "@VendorName", model.VendorName);
                    Db.AddInParameter(cmd, "@VCategoryId", model.VCategoryId);
                    Db.AddInParameter(cmd, "@BalanceDate", model.BalanceDate);
                    Db.AddInParameter(cmd, "@Payable", model.Payable);
                    Db.AddInParameter(cmd, "@Advance", model.Advance);
                    Db.AddInParameter(cmd, "@VATRate", model.VATRate);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public void AddVendorContacts(List<VendorContactsEntity> list)
        {
            var conn = Db.GetConnection() as SqlConnection;
            if (conn == null)
            {
                throw new InvalidOperationException("No Valid Database Connection!");
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var bcp = new SqlBulkCopy(conn))
            {
                var mapVContactId =
                    new SqlBulkCopyColumnMapping("VContactId", "VContactId");
                bcp.ColumnMappings.Add(mapVContactId);

                var mapVendorId =
                    new SqlBulkCopyColumnMapping("VendorId", "VendorId");
                bcp.ColumnMappings.Add(mapVendorId);

                var mapLinkman =
                    new SqlBulkCopyColumnMapping("Linkman", "Linkman");
                bcp.ColumnMappings.Add(mapLinkman);

                var mapMobilePhone =
                    new SqlBulkCopyColumnMapping("MobilePhone", "MobilePhone");
                bcp.ColumnMappings.Add(mapMobilePhone);

                var mapSpecialPlane =
                   new SqlBulkCopyColumnMapping("SpecialPlane", "SpecialPlane");
                bcp.ColumnMappings.Add(mapSpecialPlane);

                var mapQqMsn =
                   new SqlBulkCopyColumnMapping("QqMsn", "QqMsn");
                bcp.ColumnMappings.Add(mapQqMsn);

                var mapDeliveryAddress =
                   new SqlBulkCopyColumnMapping("DeliveryAddress", "DeliveryAddress");
                bcp.ColumnMappings.Add(mapDeliveryAddress);

                var mapFlag =
                   new SqlBulkCopyColumnMapping("Flag", "Flag");
                bcp.ColumnMappings.Add(mapFlag);

                bcp.DestinationTableName = "PSS_VendorContacts";

                try
                {
                    bcp.WriteToServer(Collection.ToDataTable(list));
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
        }

        public int GetTotal(VendorQcEntity qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT count(*) AS [Total] FROM PSS_Vendor AS A  ");
            strSql.Append(" LEFT JOIN PSS_VendorCategory AS B ON A.VCategoryId=B.VCategoryId ");
            strSql.Append(" LEFT JOIN (SELECT * FROM(SELECT * FROM PSS_VendorContacts WHERE Flag=1) AS X) AS T ON A.VendorId=T.VendorId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.VCategoryId))
            {
                strSql.Append(" AND A.VCategoryId=@VCategoryId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.VendorCode))
            {
                strSql.Append(" AND A.VendorCode LIKE '%" + qcmodel.VendorCode + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.VendorName))
            {
                strSql.Append(" AND A.VendorName LIKE '%" + qcmodel.VendorName + "%' ");
            }
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.VCategoryId))
                    {
                        Db.AddInParameter(cmd, "@VCategoryId", qcmodel.VCategoryId);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<VendorEntity> GetVendorList(VendorQcEntity qcmodel)
        {
            var list = new List<VendorEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.CreateTime) AS idx,A.VendorId,A.VCategoryId,B.VCategoryName,A.VendorCode,A.VendorName,T.Linkman,T.MobilePhone,T.SpecialPlane,T.QqMsn,(A.Payable-A.Advance) AS Price,A.VATRate FROM PSS_Vendor AS A  ");
            strSql.Append(" LEFT JOIN PSS_VendorCategory AS B ON A.VCategoryId=B.VCategoryId ");
            strSql.Append(" LEFT JOIN (SELECT * FROM(SELECT * FROM PSS_VendorContacts WHERE Flag=1) AS X) AS T ON A.VendorId=T.VendorId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.VCategoryId))
            {
                strSql.Append(" AND A.VCategoryId=@VCategoryId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.VendorCode))
            {
                strSql.Append(" AND A.VendorCode LIKE '%" + qcmodel.VendorCode + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.VendorName))
            {
                strSql.Append(" AND A.VendorName LIKE '%" + qcmodel.VendorName + "%' ");
            }
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.VCategoryId))
                    {
                        Db.AddInParameter(cmd, "@VCategoryId", qcmodel.VCategoryId);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new VendorEntity();
                            if (reader["VendorId"] != DBNull.Value)
                                model.VendorId = new Guid(reader["VendorId"].ToString());
                            model.VCategoryName = reader["VCategoryName"] == obj ? null : Convert.ToString(reader["VCategoryName"]);
                            model.VendorCode = reader["VendorCode"] == obj ? null : Convert.ToString(reader["VendorCode"]);
                            model.VendorName = reader["VendorName"] == obj ? null : Convert.ToString(reader["VendorName"]);
                            model.Linkman = reader["Linkman"] == obj ? null : Convert.ToString(reader["Linkman"]);
                            model.MobilePhone = reader["MobilePhone"] == obj ? null : Convert.ToString(reader["MobilePhone"]);
                            model.SpecialPlane = reader["SpecialPlane"] == obj ? null : Convert.ToString(reader["SpecialPlane"]);
                            model.QqMsn = reader["QqMsn"] == obj ? null : Convert.ToString(reader["QqMsn"]);
                            if (reader["Price"] != DBNull.Value)
                                model.Price = Convert.ToDecimal(reader["Price"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public VendorEntity GetVendorInfo(string id)
        {
            var model = new VendorEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT A.[VendorId],A.[VendorCode],A.[VendorName],A.[VCategoryId],B.[VCategoryName],A.[BalanceDate],A.[Payable],A.[Advance],A.[VATRate],A.[Remark] FROM [PSS_Vendor] AS A ");
            strSql.Append(" INNER JOIN PSS_VendorCategory AS B ON A.VCategoryId=B.VCategoryId ");
            strSql.Append(" WHERE VendorId=@VendorId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {

                try
                {
                    Db.AddInParameter(cmd, "@VendorId", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["VendorId"] != DBNull.Value)
                                model.VendorId = new Guid(reader["VendorId"].ToString());
                            model.VendorCode = reader["VendorCode"] == obj ? null : Convert.ToString(reader["VendorCode"]);
                            model.VendorName = reader["VendorName"] == obj ? null : Convert.ToString(reader["VendorName"]);
                            if (reader["VCategoryId"] != DBNull.Value)
                                model.VCategoryId = new Guid(reader["VCategoryId"].ToString());
                            model.VCategoryName = reader["VCategoryName"] == obj ? null : Convert.ToString(reader["VCategoryName"]);
                            if (reader["BalanceDate"] != DBNull.Value)
                                model.BalanceDate = Convert.ToDateTime(reader["BalanceDate"].ToString());
                            if (reader["Payable"] != DBNull.Value)
                                model.Payable = Convert.ToDecimal(reader["Payable"].ToString());
                            if (reader["Advance"] != DBNull.Value)
                                model.Advance = Convert.ToDecimal(reader["Advance"].ToString());
                            if (reader["VATRate"] != DBNull.Value)
                                model.VATRate = Convert.ToDecimal(reader["VATRate"].ToString());
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public IList<VendorContactsEntity> GetVendorContactsList(VendorContactsQcEntity qcmodel)
        {
            var list = new List<VendorContactsEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT * FROM PSS_VendorContacts WHERE VendorId=@VendorId  ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@VendorId", qcmodel.VendorId);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new VendorContactsEntity();
                            if (reader["VContactId"] != DBNull.Value)
                                model.VContactId = new Guid(reader["VContactId"].ToString());
                            model.Linkman = reader["Linkman"] == obj ? null : Convert.ToString(reader["Linkman"]);
                            model.MobilePhone = reader["MobilePhone"] == obj ? null : Convert.ToString(reader["MobilePhone"]);
                            model.SpecialPlane = reader["SpecialPlane"] == obj ? null : Convert.ToString(reader["SpecialPlane"]);
                            model.QqMsn = reader["QqMsn"] == obj ? null : Convert.ToString(reader["QqMsn"]);
                            model.DeliveryAddress = reader["DeliveryAddress"] == obj ? null : Convert.ToString(reader["DeliveryAddress"]);
                            model.SpecialPlane = reader["SpecialPlane"] == obj ? null : Convert.ToString(reader["SpecialPlane"]);
                            model.QqMsn = reader["QqMsn"] == obj ? null : Convert.ToString(reader["QqMsn"]);
                            if (reader["Flag"] != DBNull.Value)
                                model.Flag = (bool)reader["Flag"];
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool ModifyVendor(VendorEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [PSS_Vendor] SET [VendorCode] = @VendorCode,[VendorName] = @VendorName,[VCategoryId] = @VCategoryId,[BalanceDate] = @BalanceDate,[Payable] = @Payable,[Advance] = @Advance,[VATRate] = @VATRate,[Remark] = @Remark WHERE [VendorId] = @VendorId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@VendorCode", model.VendorCode);
                    Db.AddInParameter(cmd, "@VendorName", model.VendorName);
                    Db.AddInParameter(cmd, "@VCategoryId", model.VCategoryId);
                    Db.AddInParameter(cmd, "@BalanceDate", model.BalanceDate);
                    Db.AddInParameter(cmd, "@Payable", model.Payable);
                    Db.AddInParameter(cmd, "@Advance", model.Advance);
                    Db.AddInParameter(cmd, "@VATRate", model.VATRate);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@VendorId", model.VendorId);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool DeleteVendorContacts(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_VendorContacts] WHERE VendorId=@VendorId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@VendorId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveVendor(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_Vendor] WHERE VendorId=@VendorId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@VendorId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public IList<VendorEntity> GetVendorListByCombobox()
        {
            var list = new List<VendorEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT A.VendorId,A.VendorCode,A.VendorName FROM PSS_Vendor AS A Order by CreateTime ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new VendorEntity();
                            if (reader["VendorId"] != DBNull.Value)
                                model.VendorId = new Guid(reader["VendorId"].ToString());
                            model.VendorCode = reader["VendorCode"] == obj ? null : Convert.ToString(reader["VendorCode"]);
                            model.VendorName = reader["VendorName"] == obj ? null : Convert.ToString(reader["VendorName"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
    }
}
